package com.weixin.dao.impl;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.weixin.dao.Purchase_People_TbDAO;
import com.weixin.entity.Purchase_People_Tb;

import com.weixin.util.C3p0Util;

public class Purchase_People_TbDAOImpl implements Purchase_People_TbDAO{

	@Override
	public List<Purchase_People_Tb> selectAll() throws SQLException {
		// TODO Auto-generated method stub
		String sql="select * from purchase_people_tb where is_show=1";
		return C3p0Util.qr.query(sql, new BeanListHandler<Purchase_People_Tb>(Purchase_People_Tb.class));
	}


	@Override
	public boolean insert(Purchase_People_Tb purchase_people_tb) throws SQLException {
		// TODO Auto-generated method stub
		String sql="insert into purchase_people_tb(purchase_name,brithday,address,email,qq_num,weixin_num,remark,is_show)values(?,?,?,?,?,?,?,?)";
		int update = C3p0Util.qr.update(sql, purchase_people_tb.getPurchase_name(),purchase_people_tb.getBrithday(),purchase_people_tb.getAddress(),purchase_people_tb.getEmail(),purchase_people_tb.getQq_num(),purchase_people_tb.getWeixin_num(),purchase_people_tb.getRemark(),purchase_people_tb.getIs_show());
		return update>0;
	}


	@Override
	public boolean delete(int id) throws SQLException {
		// TODO Auto-generated method stub
		String sql="delete from purchase_people_tb where false=1 and purchase_people_id=?";
		int update = C3p0Util.qr.update(sql,id);
		return update>0;
	}


	@Override
	public boolean updateDelete(int  id) throws SQLException {
		// TODO Auto-generated method stub
		String sql="update purchase_people_tb set is_show=0 where purchase_people_id=?";
		int update = C3p0Util.qr.update(sql, id);
		return update>0;
	}

	
	public boolean update(Purchase_People_Tb  people)  throws SQLException{
		String sql="update purchase_people_tb set purchase_name=?,brithday=?,address=?,email=?,qq_num=?,weixin_num=?,remark=? where purchase_people_id=?";
		C3p0Util.qr.update(sql,people.getPurchase_name(),people.getBrithday(),people.getAddress(),people.getEmail(),people.getQq_num(),people.getWeixin_num(),people.getRemark(),people.getPurchase_people_id());
		return false;
		
	}

	@Override
	public Purchase_People_Tb byId(int id) throws SQLException {
		// TODO Auto-generated method stub\
		String sql="select * from purchase_people_tb where purchase_people_id=?";
		return C3p0Util.qr.query(sql, new BeanHandler<Purchase_People_Tb>(Purchase_People_Tb.class),id);
		
		
	}


	@Override
	public List<Purchase_People_Tb> getAllBypage(int pageSize, Integer pageNum) throws SQLException {
		// TODO Auto-generated method stub
		return  C3p0Util.qr.query("select * from purchase_people_tb where is_show=1 limit ?,?", new BeanListHandler<Purchase_People_Tb>(Purchase_People_Tb.class),(pageNum-1)*pageSize,pageSize);
	}


	@Override
	public int getTotal() throws SQLException {
		// TODO Auto-generated method stub
//		 (int)(long) C3p0Util.qr.query("select count(*) from student",new ScalarHandler());
		return (int)(long) C3p0Util.qr.query("select count(*) from purchase_people_tb",new ScalarHandler());
	}


	@Override
	public List<Purchase_People_Tb> listAllByRoleSearchPagination(String search, int pageSize, int pageNum)
			throws SQLException {
		// TODO Auto-generated method stub
		String sql="select * \r\n" + 
				"from purchase_people_tb "
				+ "where CONCAT(IFNULL(purchase_name,''),IFNULL(brithday,''),IFNULL(purchase_name,'')) LIKE ?"+
				"limit ?,?";
				
		return C3p0Util.qr.query(sql, new BeanListHandler<Purchase_People_Tb>(Purchase_People_Tb.class),"%"+search+"%",(pageNum-1)*pageSize,pageSize);
	}


	@Override
	public List<Purchase_People_Tb> getTotalBySearch(String search) throws SQLException {
		// TODO Auto-generated method stub
		String sql="SELECT * \r\n" + 
				"FROM purchase_people_tb\r\n" + 
				"where is_show=1 and (purchase_name like ? or address LIKE ? or email LIKE ? or qq_num LIKE ? or weixin_num LIKE ?)";
		return C3p0Util.qr.query(sql, new BeanListHandler<Purchase_People_Tb>(Purchase_People_Tb.class),"%"+search+"%","%"+search+"%","%"+search+"%","%"+search+"%","%"+search+"%");
	}
	


	
}